﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;

using Microsoft.Office.Interop.Excel;
using OA.Model;

namespace OA.Bll.Reimbursement
{
    /// <summary>
    /// 科研项目报销
    /// </summary>
    public class ExcelExportScientific
    {
        private Dictionary<string, int> DicColumnIndex = new Dictionary<string, int>();//用来存储对应的列名对应的列



        public void CreateScientificExcel(Application xapp, Worksheet xsheet, List<ReimbursementInfoView> reimbursementInfoViewColl)
        {
            List<string> listCoulmnNames = new List<string>();
            //  List<ReimbursementItemInfo> listRII = new List<ReimbursementItemInfo>();

            //foreach (var reimbursementInfoView in reimbursementInfoViewColl)
            //{
            //    foreach (ReimbursementItemInfo reimbursementItemInfo in reimbursementInfoView.ReimbursementItemInfoColl)
            //    {
            //        string nodeName = reimbursementItemInfo.CostItemNo + "  " + reimbursementItemInfo.CostItemName;
            //        if (!listCoulmnNames.Contains(nodeName))
            //        {
            //            listCoulmnNames.Add(nodeName);
            //        }
            //    }
            //}
            // listCoulmnNames = listRII.Select(a => a.CostItemNo + "  " + a.CostItemName).ToList();
            int rowIndex = 5, columnCounts = 7, initIndex = 0;
            CrateContent(xapp, xsheet, ref rowIndex, ref columnCounts, reimbursementInfoViewColl);
            //  CreateHead(xapp, xsheet, listCoulmnNames, ref rowIndex, ref columnCounts);
            //   CreateBottom(xapp, xsheet, rowIndex);

            //CreateHead(xapp, xsheet, listCoulmnNames, ref rowIndex, ref columnCounts);
            //initIndex = rowIndex;
            //CrateContent(xapp, xsheet, ref rowIndex, ref columnCounts, reimbursementInfoViewColl);

            //CreateBottom(xapp, xsheet, rowIndex);
            //Range range = xsheet.get_Range(xapp.Cells[1, 1], xapp.Cells[5, 5]);
            //range.Select();
            //range.Borders.LineStyle = 0.5; //设置边框的宽度
            //range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;
            //range.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;
            //range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;
            //range.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;

            //SetBorderColor(xapp, xsheet, xapp.get_Range(4, 1), xapp.get_Range(rowIndex, columnCounts));

        }

        private void CreateHead(Application xapp, Worksheet xsheet, List<string> listCoulmnNames, ref int rowIndex, ref int columnCounts)
        {
            //columnCounts = listCoulmnNames.Count + 8;//默认8列 加上动态列数

            //xsheet.Name = "科研标准费用" + xapp.Worksheets.Count;
            //xsheet.Cells[1, 1] = " 报     销     清     单  （科研项目）";
            //Range rangeTitle = (xsheet.get_Range(xapp.Cells[1, 1], xapp.Cells[1, 1]));
            //rangeTitle.Font.Bold = true;
            //rangeTitle.RowHeight = 20;
            //rangeTitle.Font.Size = 11;
            //rangeTitle.Font.Name = "宋体";
            //rangeTitle.Font.ColorIndex = 0;
            //rangeTitle.Select();
            //rangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            //Range range = xsheet.get_Range(xapp.Cells[1, 1], xapp.Cells[1, columnCounts]);
            //range.MergeCells = true;
            ExcelExportHelper.CreateCellTitle(xapp, xsheet, "科研标准费用", " 报     销     清     单  （科研项目）", columnCounts);

            xsheet.Cells[2, 1] = "请填写右边选项，每张报销清单只许选择一种方式（支票□ 现金□  建行卡□  光大卡□  还商务卡□   还备用金□）";// "只许选择一种方式";
            // xsheet.Cells[2, 2] = "只许选择一种方式";            
            // (xsheet.get_Range(xapp.Cells[2, 2], xapp.Cells[2, 2])).Font.ColorIndex = 3;
            // xsheet.Cells[2, 3] = "（支票□ 现金□  建行卡□  光大卡□  还商务卡□   还备用金□）";
            Range range2 = xsheet.get_Range(xapp.Cells[2, 1], xapp.Cells[2, columnCounts]);
            range2.MergeCells = true;
            range2.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            range2.EntireColumn.AutoFit();

            //ExcelExportHelper.CreateCellNoBorder(xapp, xsheet, "请填写右边选项，每张报销清单只许选择一种方式（支票□ 现金□  建行卡□  光大卡□  还商务卡□   还备用金□）",
            //    2, 1, 2, columnCounts, true);



            //xsheet.Cells[3, 1] = "          年    月     日 ";
            //(xsheet.get_Range(xapp.Cells[3, 1], xapp.Cells[3, 1])).Font.Size = 8;
            //(xsheet.get_Range(xapp.Cells[3, 1], xapp.Cells[3, 8])).MergeCells = true;

            ExcelExportHelper.CreateCellNoBorder(xapp, xsheet, "          年    月     日 ", 3, 1, 3, 8, true);

            ExcelExportHelper.CreateCellNoBorder(xapp, xsheet, "连续报销清单号：", 3, 9);
            ExcelExportHelper.CreateCell(xapp, xsheet, "序号", 4, 1);
            ExcelExportHelper.CreateCell(xapp, xsheet, "项目名称", 4, 2);
            ExcelExportHelper.CreateCell(xapp, xsheet, "员工编号", 4, 3);
            ExcelExportHelper.CreateCell(xapp, xsheet, "报销人", 4, 4);
            ExcelExportHelper.CreateCell(xapp, xsheet, "单据", 4, 5);
            ExcelExportHelper.CreateCell(xapp, xsheet, "内容", 4, 6);
            ExcelExportHelper.CreateCell(xapp, xsheet, "金额", 4, 7);
            //7为前面已固定的列数
            ExcelExportHelper.CreateCell(xapp, xsheet, "个人小计", 4, columnCounts);
            #region
            /*
            xsheet.Cells[3, 9] = "连续报销清单号：";
            (xsheet.get_Range(xapp.Cells[3, 9], xapp.Cells[3, 9])).Font.Size = 8;

            xsheet.Cells[4, 1] = "序号";
            Range range3 = xsheet.get_Range(xapp.Cells[4, 1], xapp.Cells[4, 1]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);


            xsheet.Cells[4, 2] = "项目名称";
            range3 = xsheet.get_Range(xapp.Cells[4, 2], xapp.Cells[4, 2]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);

            xsheet.Cells[4, 3] = "员工编号";
            range3 = xsheet.get_Range(xapp.Cells[4, 3], xapp.Cells[4, 3]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);

            xsheet.Cells[4, 4] = "报销人";
            range3 = xsheet.get_Range(xapp.Cells[4, 4], xapp.Cells[4, 4]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);

            xsheet.Cells[4, 5] = "单据";
            range3 = xsheet.get_Range(xapp.Cells[4, 5], xapp.Cells[4, 5]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);

            xsheet.Cells[4, 6] = "内容";
            range3 = xsheet.get_Range(xapp.Cells[4, 6], xapp.Cells[4, 6]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);

            xsheet.Cells[4, 7] = "金额";
            range3 = xsheet.get_Range(xapp.Cells[4, 7], xapp.Cells[4, 7]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);


            //8为前面已固定的列数
            //for (int i = 0; i < listCoulmnNames.Count; i++)
            //{
            //    xsheet.Cells[4, 8 + i] = listCoulmnNames[i];
            //    range3 = xsheet.get_Range(xapp.Cells[4, 8 + i], xapp.Cells[4, 8 + i]);

            //    DicColumnIndex.Add(listCoulmnNames[i], 8 + i);//存储对应列标题对应索引

            //    range3.Font.Size = 8;
            //    SetBorderColor(xapp, xsheet, range3, range3);
            //}

            xsheet.Cells[4, columnCounts] = "个人小计";
            range3 = xsheet.get_Range(xapp.Cells[4, columnCounts], xapp.Cells[4, columnCounts]);
            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);

            // rowIndex = 5;
             * */
            #endregion
        }

        private void CrateContent(Application xapp, Worksheet xwsheet, ref int rowIndex, ref int columnCounts, List<ReimbursementInfoView> reimbursementInfoViewColl)
        {
            int i = 1;
            int initRowIndex = rowIndex;
            int initColumnCount = columnCounts;
            List<string> listEmployees = new List<string>();//存储当前表插入的人员单位
            List<int> listReimburesements = new List<int>();//存储需要删除的项目编号,项目编号与报销ID一一对应,所以存储哪个都可以
            //List<ReimbursementInfoView> listRIV = new List<ReimbursementInfoView>();//用来存储已添加过的数据,用来动态添加列
            //Dictionary<int, object> dicSubTotal = new Dictionary<int, object>();//key 行, value值, 用来最后赋值小计
            List<int> listSubTotal = new List<int>();//存储小计的行

            foreach (var reimbursementInfoView in reimbursementInfoViewColl.GroupBy(a => a.EmployeeCardID))//项目分组
            {
                //ExcelExportHelper.CreateCell(xapp, xwsheet, reimbursementInfoViewColl.First(a => a.ProjectNum == reimbursementInfoView.Key).ProjectName + "  " +
                //    reimbursementInfoView.Key, rowIndex, 2);//项目编号
                int projectIndex = rowIndex;

                foreach (var infoView in reimbursementInfoView.GroupBy(a => a.ProjectNum))//人员分组
                {
                    if (listEmployees.Count == 3)//等于3需要重新创建一个表格来存储数据
                    {
                        break;
                    }

                    if (!listEmployees.Contains(reimbursementInfoView.Key))
                    {
                        listEmployees.Add(reimbursementInfoView.Key);

                    }
                    //string empcardId = infoView.Key;
                    //empcardId = empcardId.Length > 4 ? empcardId.Substring(empcardId.Length - 4) : empcardId;
                    //ExcelExportHelper.CreateCell(xapp, xwsheet, empcardId, rowIndex, 3);//员工编号
                    //ExcelExportHelper.CreateCell(xapp, xwsheet, reimbursementInfoViewColl.First(a => a.EmployeeCardID == infoView.Key).EmployeeName, rowIndex, 4);//报销人
                    int empIndex = rowIndex;

                    foreach (var view in infoView)
                    {
                        listReimburesements.Add(view.Id);

                        ExcelExportHelper.CreateCell(xapp, xwsheet, " ", rowIndex, 2);
                        ExcelExportHelper.CreateCell(xapp, xwsheet, " ", rowIndex, 3);//员工编号
                        ExcelExportHelper.CreateCell(xapp, xwsheet, " ", rowIndex, 4);//报销人


                        ExcelExportHelper.CreateCell(xapp, xwsheet, i.ToString(CultureInfo.InvariantCulture), rowIndex, 1);//序号
                        ExcelExportHelper.CreateCell(xapp, xwsheet, view.BillCount.ToString(CultureInfo.InvariantCulture), rowIndex, 5);//单据
                        ExcelExportHelper.CreateCell(xapp, xwsheet, view.Description, rowIndex, 6);//内容

                        for (int j = 0; j < view.ReimbursementItemInfoColl.Count; j++)
                        {
                            ReimbursementItemInfo rii = view.ReimbursementItemInfoColl[j];
                            string nodeName = rii.CostItemNo + "  " + rii.CostItemName;
                            //int colIndex = DicColumnIndex[nodeName];
                            // bool IsText = true;
                            for (int k = initColumnCount; k <= columnCounts; k++)//前7列不为动态值
                            {

                                //ExcelExportHelper.CreateCell(xapp, xwsheet, " ", rowIndex, k);//内容

                                Range range = xwsheet.get_Range(xapp.Cells[initRowIndex - 1, k], xapp.Cells[initRowIndex - 1, k]);

                                if (range.Text.ToString() == nodeName)
                                {
                                    ExcelExportHelper.CreateCell(xapp, xwsheet, rii.Money.ToString(), rowIndex, k);
                                    break;
                                    //  IsText = false;
                                }
                                if (k == columnCounts)
                                {
                                    columnCounts += 1;
                                    ExcelExportHelper.CreateCell(xapp, xwsheet, nodeName, initRowIndex - 1, columnCounts);
                                    ExcelExportHelper.CreateCell(xapp, xwsheet, rii.Money.ToString(), rowIndex, columnCounts);
                                    break;
                                }
                            }

                            //ExcelExportHelper.CreateCell(xapp, xwsheet, rii.Money.ToString(), rowIndex, colIndex);
                        }
                        //金额                       


                        string sumAmount = columnCounts == 7 ? " " : columnCounts == 8 ?
                            xwsheet.get_Range(xapp.Cells[rowIndex, 8], xapp.Cells[rowIndex, 8]).Text.ToString() :
                            "=SUM(" + ExcelExportHelper.IndexToColumn(8) + rowIndex + ":" + ExcelExportHelper.IndexToColumn(columnCounts) + rowIndex +
                                          ")";
                        ExcelExportHelper.CreateCell(xapp, xwsheet, sumAmount, rowIndex, 7);//金额
                        rowIndex++;
                        i++;
                    }

                    //string empcardId = infoView.Key;
                    //empcardId = empcardId.Length > 4 ? empcardId.Substring(empcardId.Length - 4) : empcardId;
                    //ExcelExportHelper.CreateCell(xapp, xwsheet, empcardId, empIndex, 3);//员工编号
                    //ExcelExportHelper.CreateCell(xapp, xwsheet, reimbursementInfoViewColl.First(a => a.EmployeeCardID == infoView.Key).EmployeeName, empIndex, 4);//报销人


                    ExcelExportHelper.CreateCell(xapp, xwsheet, reimbursementInfoViewColl.First(a => a.ProjectNum == infoView.Key).ProjectName + "  " +
                       infoView.Key, empIndex, 2);//项目编号



                    if (listEmployees.Count == 3)//等于3需要重新创建一个表格来存储数据
                    {
                        break;
                    }
                }

                string empcardId = reimbursementInfoView.Key;
                empcardId = empcardId.Length > 4 ? empcardId.Substring(empcardId.Length - 4) : empcardId;
                ExcelExportHelper.CreateCell(xapp, xwsheet, empcardId, projectIndex, 3);//员工编号
                ExcelExportHelper.CreateCell(xapp, xwsheet, reimbursementInfoViewColl.First(a => a.EmployeeCardID == reimbursementInfoView.Key).EmployeeName, projectIndex, 4);//报销人

                //ExcelExportHelper.CreateCell(xapp, xwsheet, reimbursementInfoViewColl.First(a => a.ProjectNum == reimbursementInfoView.Key).ProjectName + "  " +
                // reimbursementInfoView.Key, projectIndex, 2);//项目编号
                //小计
                listSubTotal.Add(rowIndex);
                if (listEmployees.Count == 3)//等于3需要重新创建一个表格来存储数据
                {
                    break;
                }
                //columnCounts += 1;//小计需要在列数基础上加1
                //string subtotal2 = (rowIndex - 1) == initRowIndex ? xwsheet.get_Range(xapp.Cells[initRowIndex, 7], xapp.Cells[initRowIndex, 7]).Text.ToString() :
                //    "=SUM(" + ExcelExportHelper.IndexToColumn(7) + initRowIndex + ":" + ExcelExportHelper.IndexToColumn(7) + (rowIndex - 1) + ")";//7 
                //ExcelExportHelper.CreateCell(xapp, xwsheet, subtotal2, rowIndex - 1, columnCounts);//小计
            }
            //小计需要列加一行
            columnCounts += 1;
            int total = 0;
            string subtotal2 = string.Empty;
            foreach (int index in listSubTotal)
            {
                if (total == 0)
                    subtotal2 = (index - 1) == initRowIndex ? xwsheet.get_Range(xapp.Cells[initRowIndex, 7], xapp.Cells[initRowIndex, 7]).Text.ToString() :
                    "=SUM(" + ExcelExportHelper.IndexToColumn(7) + initRowIndex + ":" + ExcelExportHelper.IndexToColumn(7) + (index - 1) + ")";//7 
                else
                    subtotal2 = (index - 1) == total ? xwsheet.get_Range(xapp.Cells[total, 7], xapp.Cells[total, 7]).Text.ToString() :
                  "=SUM(" + ExcelExportHelper.IndexToColumn(7) + (total) + ":" + ExcelExportHelper.IndexToColumn(7) + (index - 1) + ")";//7 
                ExcelExportHelper.CreateCell(xapp, xwsheet, subtotal2, index - 1, columnCounts);//小计
                total = index;
            }


            for (int k = initRowIndex; k <= rowIndex; k++)
            {
                for (int j = 1; j <= columnCounts; j++)
                {

                    if (k == rowIndex)
                    {
                        ExcelExportHelper.CreateCell(xapp, xwsheet, "  ", rowIndex, j);
                        if (j >= 5)
                        {
                            if (j == 6)
                                ExcelExportHelper.CreateCell(xapp, xwsheet, "合计", rowIndex, j);
                            else
                            {
                                string subtotal;
                                if (initRowIndex == (rowIndex - 1))
                                {
                                    subtotal =
                                        xwsheet.get_Range(xapp.Cells[initRowIndex, j], xapp.Cells[initRowIndex, j]).Text
                                            .ToString();
                                }
                                else
                                    subtotal = string.Format("=SUM({0}:{1})",
                                                                    ExcelExportHelper.IndexToColumn(j) + initRowIndex,
                                                                    ExcelExportHelper.IndexToColumn(j) + (rowIndex - 1));
                                ExcelExportHelper.CreateCell(xapp, xwsheet, subtotal, rowIndex, j);
                            }
                        }
                    }
                    else if (j > initColumnCount)
                    {
                        Range range3 = (Range)xapp.Cells[k, j];
                        if (range3.Value2 == null)
                            ExcelExportHelper.CreateCell(xapp, xwsheet, "  ", k, j);
                        //else
                        //Range range3 = (Range)xapp.Cells[k, j];
                        //SetBorderColor(xapp, xwsheet, range3, range3);
                    }
                }
                //ExcelExportHelper.CreateCell(xapp, xwsheet, "", rowIndex, j);
                //if (j >= 5)
                //{
                //    if (j == 6)
                //        ExcelExportHelper.CreateCell(xapp, xwsheet, "合计", rowIndex, j);
                //    else
                //    {
                //        string subtotal = string.Format("=SUM({0}:{1})",
                //                                        ExcelExportHelper.IndexToColumn(j) + initRowIndex,
                //                                        ExcelExportHelper.IndexToColumn(j) + (rowIndex - 1));
                //        ExcelExportHelper.CreateCell(xapp, xwsheet, subtotal, rowIndex, j);
                //    }
                //}
            }
            ExcelExportHelper.CreateCell(xapp, xwsheet, " ", rowIndex, columnCounts);
            rowIndex++;


            //将结构构建放入这里
            CreateHead(xapp, xwsheet, null, ref rowIndex, ref columnCounts);
            CreateBottom(xapp, xwsheet, rowIndex);



            if (listEmployees.Count >= 3)
            {
                reimbursementInfoViewColl.RemoveAll(a => listReimburesements.Count(b => a.Id == b) > 0);
                //移除已经写入过的数据
                if (reimbursementInfoViewColl.Count != 0)
                    CreateScientificExcel(xapp, (Worksheet)xapp.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing),
                                          reimbursementInfoViewColl);
            }

            #region

            /*
            for (int i = 0; i < reimbursementInfoViewColl.Count; i++)
            {
                ReimbursementInfoView infoview = reimbursementInfoViewColl[i];
                ExcelExportHelper.CreateCell(xapp, xwsheet, (i + 1).ToString(CultureInfo.InvariantCulture), i + rowIndex, 1);//序号
                ExcelExportHelper.CreateCell(xapp, xwsheet, infoview.ProjectName + "  " + infoview.ProjectNum, i + rowIndex, 2);
                string empcardId = infoview.EmployeeCardID;
                empcardId = empcardId.Length > 4 ? empcardId.Substring(empcardId.Length - 5) : empcardId;
                ExcelExportHelper.CreateCell(xapp, xwsheet, empcardId, i + rowIndex, 3);
                ExcelExportHelper.CreateCell(xapp, xwsheet, infoview.EmployeeName, i + rowIndex, 4);
                ExcelExportHelper.CreateCell(xapp, xwsheet, infoview.BillCount.ToString(CultureInfo.InvariantCulture), i + rowIndex, 5);
                ExcelExportHelper.CreateCell(xapp, xwsheet, infoview.Description, i + rowIndex, 6);
                //金额
                string sumAmount = "=SUM(" + ExcelExportHelper.IndexToColumn(7) + (i + rowIndex) + "," + ExcelExportHelper.IndexToColumn(columnCounts - 1) + (i + rowIndex) +
                                   ")";
                ExcelExportHelper.CreateCell(xapp, xwsheet, sumAmount, i + rowIndex, 7);

                for (int j = 0; j < reimbursementInfoViewColl[i].ReimbursementItemInfoColl.Count; j++)
                {
                    ReimbursementItemInfo rii = reimbursementInfoViewColl[i].ReimbursementItemInfoColl[i];
                    string nodeName = rii.CostItemNo + "  " + rii.CostItemName;
                    int colIndex = DicColumnIndex[nodeName];
                    ExcelExportHelper.CreateCell(xapp, xwsheet, rii.Money.ToString(), i + rowIndex, colIndex);
                }
            }
             * **/

            #endregion
        }

        private void CreateBottom(Application xapp, Worksheet xwsheet, int rowIndex)
        {

            ExcelExportHelper.CreateCellNoBorder(xapp, xwsheet, "技术管理中心：", rowIndex, 3);
            ExcelExportHelper.CreateCellNoBorder(xapp, xwsheet, "资产财务部：", rowIndex, 6);
            ExcelExportHelper.CreateCellNoBorder(xapp, xwsheet, "财务会计复核：", rowIndex, 8);
            ExcelExportHelper.CreateCellNoBorder(xapp, xwsheet, "核算员审核：", rowIndex, 10);

            #region
            /*
            xapp.Cells[rowIndex, 3] = "技术管理中心：";
            Range range3 = xwsheet.get_Range(xapp.Cells[rowIndex, 3], xapp.Cells[rowIndex, 3]);
            range3.Font.Size = 8;
            // SetBorderColor(_app, _xsheet, range3, range3);
            

            xwsheet.Cells[rowIndex, 6] = "资产财务部：";
            range3 = xwsheet.get_Range(xapp.Cells[rowIndex, 6], xapp.Cells[rowIndex, 6]);
            range3.Font.Size = 8;
            // SetBorderColor(_app, _xsheet, range3, range3);

            xwsheet.Cells[rowIndex, 8] = "财务会计复核：";
            range3 = xwsheet.get_Range(xapp.Cells[rowIndex, 8], xapp.Cells[rowIndex, 8]);
            range3.Font.Size = 8;
            //SetBorderColor(_app, _xsheet, range3, range3);


            xwsheet.Cells[rowIndex, 10] = "核算员审核：";
            range3 = xwsheet.get_Range(xapp.Cells[rowIndex, 10], xapp.Cells[rowIndex, 10]);
            range3.Font.Size = 8;
             * */
            #endregion
        }



        private void SetBorderColor(Microsoft.Office.Interop.Excel.Application excel, _Worksheet xSt, Range sRange, Range tRange)
        {
            Range ranges = xSt.get_Range(sRange, tRange);
            ranges.Select();
            ranges.EntireColumn.AutoFit();//自动调整列宽\
            //xSt.get_Range(sRange, tRange).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            tRange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            ranges.Borders.LineStyle = 0.5; //设置边框的宽度

            ranges.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;
            ranges.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;
            ranges.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;
            ranges.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
        }
    }
}
